const sql = require('mssql');
const baseFn = require('../../public/base.js');


/**
 * 返利设置
 */

const getReturnSettingShop = async (ctx) => {
    const { index, pageSize, searchList } = ctx.request.body;
    let selectCountSql = ` SELECT COUNT(F.YPBM) AS TOTAL FROM FLSZ F LEFT JOIN BM_YP B ON F.YPBM = B.BM LEFT JOIN BM_WLDW DW ON F.GYSBM = DW.BM  WHERE 1=1 `;
    let selectSql = ` SELECT * FROM ( SELECT  ROW_NUMBER() OVER(ORDER BY F.YPBM) AS PX,F.JBR,YG.MC AS JBRMC,F.GYSBM AS BM,F.FLFS,F.JSFS,F.FLBL,F.FLKSRQ,F.FLJSRQ,F.FLJS,F.JJRQ,F.YJTS,F.QYZT,F.TDZ,F.PPFL,F.FLTJ,
    DW.MC,B.BM AS YPBM,B.MC AS YPMC,B.GG AS YPGG,B.JLDW,B.SCCJ,B.PZWH FROM FLSZ F LEFT JOIN BM_YG YG ON YG.BM = F.JBR LEFT JOIN BM_YP B ON F.YPBM = B.BM LEFT JOIN BM_WLDW DW ON F.GYSBM = DW.BM WHERE 1=1`;
    if (searchList.GYS) {
        let publicSearchSql = ` AND (DW.BM LIKE  '%${searchList.GYS}%' OR  DW.MC LIKE  '%${searchList.GYS}%' OR DW.JP LIKE  '%${searchList.GYS}%') `;
        selectSql += publicSearchSql;
        selectCountSql += publicSearchSql;
    }

    if (searchList.YPBM) {
        let publicSearchSql = ` AND (B.BM LIKE  '%${searchList.YPBM}%' OR  B.MC LIKE  '%${searchList.YPBM}%' OR B.JP LIKE  '%${searchList.YPBM}%') `;
        selectSql += publicSearchSql;
        selectCountSql += publicSearchSql;
    }

    selectSql += `)  T2 WHERE T2.PX between ${(index - 1) * pageSize + 1} and ${index * pageSize}`;
    let result = await sql.query(selectSql);
    let totalResult = await sql.query(selectCountSql);
    ctx.body = {
        result: result.recordset,
        total: totalResult.recordset[0].TOTAL
    }
}

// 保存返利设置的信息
const keepReturnSettingInfo = async (ctx) => {
    try {
        const body = ctx.request.body;
        let actionSqlList = [];
        for (let item of body) {
            let selectSql = `SELECT COUNT(YPBM) AS NUM FROM FLSZ WHERE GYSBM = '${item.GYSBM}' AND YPBM = '${item.YPBM}'`;
            let selectResult = await sql.query(selectSql);
            let actoinSql = '';
            if (selectResult.recordset[0].NUM > 0) {
                actoinSql = `UPDATE FLSZ SET JBR='${item.JBR}',FLFS='${item.FLFS}',TDZ=${item.TDZ ? item.TDZ : 0},JSFS='${item.JSFS}',FLBL='${item.FLBL}',FLKSRQ='${item.FLKSRQ}',FLJSRQ='${item.FLJSRQ}',
            FLJS='${item.FLJS}',JJRQ='${item.JJRQ}',YJTS=${item.YJTS},QYZT='${item.QYZT}',PPFL='${item.PPFL}',FLTJ='${item.FLTJ}' WHERE  GYSBM = '${item.GYSBM}' AND YPBM = '${item.YPBM}'`;
            } else {
                actoinSql = `INSERT INTO FLSZ (GYSBM,YPBM,FLFS,JSFS,FLBL,FLKSRQ,FLJSRQ,FLJS,JJRQ,YJTS,TDZ,QYZT,PPFL,FLTJ,JBR) VALUES  ('${item.GYSBM}','${item.YPBM}','${item.FLFS}','${item.JSFS}','${item.FLBL}','${item.FLKSRQ}','${item.FLJSRQ}',
            '${item.FLJS}','${item.JJRQ}',${item.YJTS},${item.TDZ ? item.TDZ : 0},'${item.QYZT}','${item.PPFL}','${item.FLTJ}','${item.JBR}') `;
            }
            actionSqlList.push(actoinSql);
        };
        let lastSql = `SET XACT_ABORT ON Begin Tran ${actionSqlList.join(' ')}  Commit Tran `;
        let result = await sql.query(lastSql);
        if (result.rowsAffected[0] > 0) {
            ctx.body = {
                success: true
            }
        } else {
            ctx.body = {
                success: false
            }
        }
    } catch (err) {
        ctx.body = {
            success: false,
            err
        }
    }

}

// 获取供应商信息
const getAddRatioStoreInfo = async (ctx) => {
    const { type, store, ypbm } = ctx.request.body;
    let selectSql = '';
    if (type === 'part') {
        selectSql = `SELECT RZ.DWMC AS MC,RZ.DWBM AS BM FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID LEFT JOIN BM_WLDW DW ON 
        RZ.DWBM = DW.BM WHERE DW.QY_FLAG = 'T' AND (DW.DWLX = 0 OR DW.DWLX = 1) AND RM.YPBM = '${ypbm}'  `;
        if (store) {
            selectSql += ` AND ( DW.BM LIKE '%${store}%' OR DW.MC LIKE '%${store}%' OR DW.JP LIKE '%${store}%' ) `;
        }
        selectSql += ` GROUP BY RM.YPBM,RZ.DWBM,RZ.DWMC `;
    } else {
        selectSql = `SELECT DW.BM,DW.MC FROM BM_WLDW DW WHERE DW.QY_FLAG = 'T' AND (DW.DWLX = 0 OR DW.DWLX = 1) `;
        if (store) {
            selectSql += ` AND ( DW.BM LIKE '%${store}%' OR DW.MC LIKE '%${store}%' OR DW.JP LIKE '%${store}%' ) `;
        }
    }
    let selectResult = await sql.query(selectSql);
    ctx.body = {
        result: selectResult.recordset
    }

}

// 获取选择供应商的返利信息
const getChooseShopReturnInfo = async (ctx) => {
    const { YPBM, storeList } = ctx.request.body;
    let selectSql = `SELECT * FROM FLSZ F WHERE F.YPBM = '${YPBM}' AND F.GYSBM IN (${storeList.join(',')}) `;
    let selectResult = await sql.query(selectSql);
    ctx.body = {
        result: selectResult.recordset
    }

}

/**
 * 
 * 获取筛选的商编码 
 */
const getReturnSettingSupplierNum = async (ctx, next) => {
    const { val, index, pageSize } = ctx.request.body;
    let totalSql = ` SELECT COUNT(Y.BM) AS NUM FROM BM_YP Y `;
    let actionSql = `SELECT * FROM( SELECT ROW_NUMBER() OVER(ORDER BY Y.BM) AS PX,Y.BM,Y.TM,Y.MC,Y.TYMC,Y.JXMC,Y.JLDW,Y.SCCJ,Y.PZWH,Y.YPLX,Y.GG,Y.JP FROM BM_YP Y  `;
    if (val) {
        let publicSql = ` WHERE Y.QY_FLAG = 'T' AND (Y.BM LIKE '%${val}%' OR Y.MC LIKE '%${val}%'  OR Y.JP LIKE '%${val}%') `;
        totalSql += publicSql;
        actionSql += publicSql;
    }
    actionSql += ` )  T2 WHERE T2.PX between ${(index - 1) * pageSize + 1} and ${index * pageSize} `;
    let totalResult = await sql.query(totalSql);
    let actionResult = await sql.query(actionSql);
    ctx.body = { total: totalResult.recordset[0].NUM, result: actionResult.recordset }
}

/**
 * 
 * 获取筛选的商编码 
 */
const getReturnSettingInfo = async (ctx, next) => {
    try {
        const { YPBM, startTime, endTime } = ctx.request.body;
        let selectSql = ` SELECT  RZ.RQ,RZ.DWMC,RM.DJ,RM.YPBM,RM.YPMC FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID WHERE RM.YPBM = '${YPBM}'   `;
        if (endTime) {
            selectSql += ` AND RZ.RQ <= '${baseFn.getNowTimeId(endTime).halfDate} 23:59:59' `;
        }
        if (startTime) {
            selectSql += ` AND RZ.RQ >= '${baseFn.getNowTimeId(startTime).halfDate} 00:00:00' `;
        }
        let result = await sql.query(selectSql);

        if (result.rowsAffected[0] > 0) {
            ctx.body = { code: 200, message: '获取数据成功', data: { result: result.recordset } }
        } else {
            ctx.body = { code: 400, message: '没有获取到相关数据' }
        }

    } catch (err) {
        ctx.body = { code: 500, err }
    }
}


module.exports = {
    'POST /getReturnSettingShop': getReturnSettingShop,
    'POST /getAddRatioStoreInfo': getAddRatioStoreInfo,
    'POST /getChooseShopReturnInfo': getChooseShopReturnInfo,
    'POST /getReturnSettingSupplierNum': getReturnSettingSupplierNum,
    'POST /getReturnSettingInfo': getReturnSettingInfo,
    'POST /keepReturnSettingInfo': keepReturnSettingInfo
}
